using GymTimer.Models;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Logging;


namespace GymTimer.Data
{
    /// <summary>
    /// Repository class for managing tasks in the database.
    /// </summary>
    public class ExerciseRepository
    {
        private bool _hasBeenInitialized = false;
        private readonly ILogger _logger;

        /// <summary>
        /// Initializes a new instance of the <see cref="ExerciseRepository"/> class.
        /// </summary>
        /// <param name="logger">The logger instance.</param>
        public ExerciseRepository(ILogger<ExerciseRepository> logger)
        {
            _logger = logger;
        }

        /// <summary>
        /// Initializes the database connection and creates the Task table if it does not exist.
        /// </summary>
        private async Task Init()
        {
            if (_hasBeenInitialized)
                return;

            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            try
            {
                var createTableCmd = connection.CreateCommand();
                createTableCmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS Exercise (
                ID INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                Round INTEGER NOT NULL,
                RestTime INTEGER NOT NULL,
                ExerciseTime INTEGER NOT NULL,
                LastRestTime INTEGER NOT NULL,                
                ScheduleID INTEGER NOT NULL

            );";
                await createTableCmd.ExecuteNonQueryAsync();
            }
            catch (Exception e)
            {
                _logger.LogError(e, "Error creating Exercise table");
                throw;
            }

            _hasBeenInitialized = true;
        }

        /// <summary>
        /// Retrieves a list of all tasks from the database.
        /// </summary>
        /// <returns>A list of <see cref="Exercise"/> objects.</returns>
        public async Task<List<Exercise>> ListAsync()
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var selectCmd = connection.CreateCommand();
            selectCmd.CommandText = "SELECT * FROM Exercise";
            var tasks = new List<Exercise>();

            await using var reader = await selectCmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                tasks.Add(new Exercise
                {
                    ID = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Round = reader.GetInt32(2),
                    RestTime = reader.GetInt32(3),
                    ExerciseTime = reader.GetInt32(4),
                    LastRestTime = reader.GetInt32(5),
                    ScheduleID=reader.GetInt32(6),
                });
            }

            return tasks;
        }


        /// <summary>
        /// Retrieves a list of tasks associated with a specific project.
        /// </summary>
        /// <param name="scheduleId">The ID of the project.</param>
        /// <returns>A list of <see cref="Exercise"/> objects.</returns>
        public async Task<List<Exercise>> ListAsync(int scheduleId)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var selectCmd = connection.CreateCommand();
            selectCmd.CommandText = "SELECT * FROM Exercise WHERE ScheduleID = @projectId";
            selectCmd.Parameters.AddWithValue("@projectId", scheduleId);
            var tasks = new List<Exercise>();

            await using var reader = await selectCmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                tasks.Add(new Exercise
                {
                    ID = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Round = reader.GetInt32(2),
                    RestTime = reader.GetInt32(3),
                    ExerciseTime = reader.GetInt32(4),
                    LastRestTime = reader.GetInt32(5),
                    ScheduleID = reader.GetInt32(6),
                });
            }

            return tasks;
        }


        /// <summary>
        /// Retrieves a specific task by its ID.
        /// </summary>
        /// <param name="id">The ID of the task.</param>
        /// <returns>A <see cref="Exercise"/> object if found; otherwise, null.</returns>
        public async Task<Exercise?> GetAsync(int id)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var selectCmd = connection.CreateCommand();
            selectCmd.CommandText = "SELECT * FROM Exercise WHERE ID = @id";
            selectCmd.Parameters.AddWithValue("@id", id);

            await using var reader = await selectCmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                return new Exercise
                {
                    ID = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Round = reader.GetInt32(2),
                    RestTime = reader.GetInt32(3),
                    ExerciseTime = reader.GetInt32(4),
                    LastRestTime = reader.GetInt32(5)
                };
            }

            return null;
        }

        /// <summary>
        /// Saves a task to the database. If the task ID is 0, a new task is created; otherwise, the existing task is updated.
        /// </summary>
        /// <param name="item">The task to save.</param>
        /// <returns>The ID of the saved task.</returns>
        /// 


        public async Task<int> SaveItemAsync(Exercise item)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var saveCmd = connection.CreateCommand();
            if (item.ID == 0)
            {
                saveCmd.CommandText = @"
            INSERT INTO Task (Name, Round, RestTime,ExerciseTime,LastRestTime) VALUES (@name, @round, @restTime,@exerciseTime,@lastRestTime);
            SELECT last_insert_rowid();";
            }
            else
            {
                saveCmd.CommandText = @"
            UPDATE Task SET Name = @name, Round = @round, RestTime = @restTime,ExerciseTime=@exerciseTime,LastRestTime=@lastRestTime  WHERE ID = @id";
                saveCmd.Parameters.AddWithValue("@id", item.ID);
            }

            saveCmd.Parameters.AddWithValue("@name", item.Name);
            saveCmd.Parameters.AddWithValue("@round", item.Round);
            saveCmd.Parameters.AddWithValue("@restTime", item.RestTime);
            saveCmd.Parameters.AddWithValue("@exerciseTime", item.ExerciseTime);
            saveCmd.Parameters.AddWithValue("@lastRestTime", item.LastRestTime);

            var result = await saveCmd.ExecuteScalarAsync();
            if (item.ID == 0)
            {
                item.ID = Convert.ToInt32(result);
            }

            return item.ID;
        }

        /// <summary>
        /// Deletes a task from the database.
        /// </summary>
        /// <param name="item">The task to delete.</param>
        /// <returns>The number of rows affected.</returns>
        public async Task<int> DeleteItemAsync(Exercise item)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var deleteCmd = connection.CreateCommand();
            deleteCmd.CommandText = "DELETE FROM Exercise WHERE ID = @id";
            deleteCmd.Parameters.AddWithValue("@id", item.ID);

            return await deleteCmd.ExecuteNonQueryAsync();
        }

        /// <summary>
        /// Drops the Task table from the database.
        /// </summary>
        public async Task DropTableAsync()
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var dropTableCmd = connection.CreateCommand();
            dropTableCmd.CommandText = "DROP TABLE IF EXISTS Exercise";
            await dropTableCmd.ExecuteNonQueryAsync();
            _hasBeenInitialized = false;
        }
    }
}